/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package util;

import commonpackage.Eating;
import commonpackage.Hosting;
import commonpackage.Order;
import commonpackage.POI;
import commonpackage.Travel;
import java.util.Calendar;
import java.util.GregorianCalendar;

/**
 *
 * @author chuda
 */
public class SQLHelper {

    public static String deleteOrderByID(int orderid){
        return "DELETE FROM ORDERS WHERE "+Helper.ORDERID+"="+orderid;
    }
    /*
     * Udpate accomodation statement
     **/
    public static String updateAccomodation(int poiid, int accomodation){
        String sql = "";
        //update pois set num_available_rooms=22 where poiid=1
        sql += "UPDATE POIS SET "+ Helper.ACCOMODATION+"="+accomodation+" WHERE POIID="+poiid;
        System.out.println("update accomodation sql: "+sql);
        return sql;
    }
    /*
     * Uptate number of available rooms statement
     **/
    public static String updateRooms(int poiid, int rooms){
        String sql = "";
         sql += "UPDATE POIS SET "+ Helper.NUM_AVAILABLE_ROOMS+"="+rooms+" WHERE POIID="+poiid;
         System.out.println("update rooms sql: "+sql);
         return sql;
    }

    /*Select poi by id*/
    public static String selectPOIbyID(int id) {
        String sql = "";
        sql += "SELECT * FROM POIS WHERE "+Helper.POIID+"="+id;
        System.out.println("select poi by id sql: "+sql);
        return sql;
    }
    /*Inserting an order*/
    public static String insertOrder(Order order){
       // insert into orders(poiid,mobilenumber,num_people,DATE,name_orderer)values(1,'24234',2,current_date,'olga')
       String sql = "";
       sql += "INSERT INTO ORDERS ("+Helper.POIID+","+
               Helper.MOBILENUM+"," +
               Helper.NUM_PEOPLE+","+
               Helper.DATE+","+
               Helper.NAME_ORDERER+
               ") VALUES(";

       sql += order.getPoiid()+ ",";
       sql += "'"+order.getTelNumber()+"'" + ",";
       sql += order.getNumPeople()+",";
       sql += " current_date, ";
       sql += "'"+order.getNameOrderer()+"'";
       sql += ")";
        System.out.println("insert order sql: "+sql);
       return sql;
    }


     /*Inserting an order*/
     public static String insertOrder2(Order order){
       // insert into orders(poiid,mobilenumber,num_people,DATE,name_orderer)values(1,'24234',2,current_date,'olga')
       String sql = "";
      // String date = order.getResDate().toString();
       Calendar cal = new GregorianCalendar();
       cal.setTime(order.getResDate());
       int year = cal.get(Calendar.YEAR);
       int month = cal.get(Calendar.MONTH)+1;
       int day = cal.get(Calendar.DAY_OF_MONTH);
       int hour = cal.get(Calendar.HOUR_OF_DAY);
       int minute = cal.get(Calendar.MINUTE);

        System.out.println("year "+year+" m "+month+" d"+day+" hour: "+hour+" min: "+minute);
        String y,m,d, h, min;
        y= Integer.toString(year);
        if (month<10)
            m = "0"+month;
        else
            m = Integer.toString(month);
        if(day < 10)
            d = "0" + day;
        else
            d = Integer.toString(day);
         if(hour < 10)
            h = "0" + hour;
        else
            h = Integer.toString(hour);
         if(minute < 10)
            min = "0" + minute;
        else
            min = Integer.toString(minute);
            String date = y+m+d+h+min+"00";

       sql += "INSERT INTO ORDERS ("+Helper.POIID+","+
               Helper.MOBILENUM+"," +
               Helper.NUM_PEOPLE+","+
               Helper.DATE+","+
               Helper.NAME_ORDERER+
               ") VALUES(";

       sql += order.getPoiid()+ ",";
       sql += "'"+order.getTelNumber()+"'" + ",";
       sql += order.getNumPeople()+",";
       sql += "'"+date+"'"+",";
       sql += "'"+order.getNameOrderer()+"'";
       sql += ")";
        System.out.println("insert order sql: "+sql);
       return sql;
    }

    /*Select * from pois*/
    public static String selectAllPOIs(){
        return "SELECT * FROM POIS";
    }
    /*SQL of select orders statement*/
    public static String selectOrdersByMobNumber(String mobNumber){
        String sql = "";
        sql += "SELECT * FROM ORDERS WHERE "+Helper.MOBILENUM+"=";
        sql += "'"+mobNumber+"'";
        return sql;
    }
    /*SQL of select by location*/
    public static String selectPOIByLocation(String location) {
        String sql = "";
        sql += "SELECT * FROM POIS WHERE ADDRESS LIKE '%"+location+"%' OR CITY LIKE '%"+location+"%'";
        System.out.println("select POI by location sql: "+sql);
        return sql;
    }
    /*Generates SQL statement of select poi by keyword*/
    public static String selectPOI (String keyword){
        String sql = "";
        sql += "SELECT * FROM POIS WHERE ADDRESS LIKE '%"+keyword+"%' OR NAME LIKE '%"+keyword+"%' OR DESCRIPTION LIKE '%"+keyword+"%' OR CITY LIKE '%"+keyword+"%' OR COUNTRY LIKE " +
                "'%"+keyword+"%'";
        System.out.println("select POI by kw sql: "+sql);
        return sql;
    }

    /*Generates SQL statement of select Eating by keyword*/
    public static String selectEating (String keyword){
        String sql = "";
        sql += "SELECT * FROM POIS WHERE CLASSIFICATION = 'EATING' and (DESCRIPTION LIKE '%"+keyword+"%' OR NAME LIKE '%"+keyword+"%' OR ADDRESS LIKE '%"+keyword+"%' OR CITY LIKE '%"+keyword+"%' OR COUNTRY LIKE " +
                "'%"+keyword+"%')";
        System.out.println("select eating by kw sql: "+sql);
        return sql;
    }

     /*Generates SQL statement of select Travel by keyword*/
    public static String selectTravel (String keyword){
        String sql = "";
        sql += "SELECT * FROM POIS WHERE CLASSIFICATION = 'TRAVEL' and (DESCRIPTION LIKE '%"+keyword+"%' OR NAME LIKE '%"+keyword+"%' OR ADDRESS LIKE '%"+keyword+"%' OR CITY LIKE '%"+keyword+"%' OR COUNTRY LIKE " +
                "'%"+keyword+"%')";
        System.out.println("select travel by kw sql: "+sql);
        return sql;
    }

     /*Generates SQL statement of select Travel by keyword*/
    public static String selectHosting (String keyword){
        String sql = "";
        sql += "SELECT * FROM POIS WHERE CLASSIFICATION = 'HOSTING' and (DESCRIPTION LIKE '%"+keyword+"%' OR NAME LIKE '%"+keyword+"%' OR ADDRESS LIKE '%"+keyword+"%' OR CITY LIKE '%"+keyword+"%' OR COUNTRY LIKE " +
                "'%"+keyword+"%')";
        System.out.println("select hosting by kw sql: "+sql);
        return sql;
    }


    /*SQL for insert POI*/
    public static String insertPOI (POI poi) {
        String sql = "";
        sql += " INSERT INTO "+Helper.POIS+" ("+
                Helper.NAME+","+
         Helper.DESCRIPTION+","+Helper.TELEPHONE+","
         +Helper.ADDRESS+","+Helper.TICKET+","+Helper.LATITUDE+","+
         Helper.LONGITUDE+","+Helper.TYPE+","+Helper.URL+","+Helper.CLASSIFICATION+","

        /*Eating*/
         +Helper.MIN_PRICE+","+
         Helper.MAX_PRICE+","+
         Helper.AVG_PRICE+","+
         Helper.ACCOMODATION+","
         /*Travel*/
         +Helper.COUNTRY+","+Helper.CITY+","+Helper.PRICE+","+
         /*Hosting*/
         Helper.MIN_PRICE_NIGHT+","+Helper.MAX_PRICE_NIGHT+","+
         Helper.NUM_AVAILABLE_ROOMS+","+Helper.LUNCH;
        sql += ")";
        sql += " values(";

        sql += "'"+poi.getName()+"'"+",";
        sql += "'"+poi.getDescription()+"'"+",";
        sql += "'"+poi.getTelephone()+"'"+",";
        sql += "'"+poi.getAddress()+"'"+",";
        sql += poi.isTicket()+",";
        sql += poi.getPosition().getLatitude()+",";
        sql += poi.getPosition().getLongitude()+",";
        sql += "'"+poi.getType()+"'"+",";
        sql += "'"+poi.getUrl()+"'"+",";
        if(poi instanceof Eating) {
            sql += "'"+Helper.EATING+"'"+",";
            Eating eating = (Eating)poi;
            sql += eating.getMin_price() + ",";
            sql += eating.getMax_price() + ",";
            sql += eating.getAvg_price() + ",";
            sql += eating.getAccomodation() + ",";
            for(int i= 0; i<6; i++)
                sql += null+",";
            sql += null;

        }else
            if(poi instanceof Travel) {
                sql += "'"+Helper.TRAVEL+"'"+",";
                Travel travel = (Travel)poi;
                for(int i= 0; i< 4; i++)
                    sql += null + ",";
                sql += "'"+travel.getCountry()+"'"+",";
                sql += "'"+travel.getCity()+"'"+",";
                sql += travel.getPrice()+",";
                for(int i= 0; i<3; i++)
                    sql += null + ",";
                sql += null;
            }else
                if(poi instanceof Hosting) {
                    sql += "'"+Helper.HOSTING+"'"+",";
                    Hosting hosting = (Hosting)poi;
                    for(int i= 0; i< 7; i++)
                        sql += null + ",";
                    sql += hosting.getMin_price_night() + ",";
                    sql += hosting.getMax_price_night() + ",";
                    sql += hosting.getNum_available_rooms() + ",";
                    sql += hosting.isLunch_included();
                }
                sql += ")";
          System.out.println("SQL: "+sql);
       // sql += poi.getType()
       // if(poi.getClass().equals(Eating.class))

        return sql;
    }
}
